const _ = require("underscore");
const moment = require("moment/moment");
const deviceTypeInfo = {
    water:{
        label:"water_consumption",
        tableList:["water"],
    },
    heat:{
        label:"consumption_heat",
        tableList:["heat","heating_cooling_meter"],
    },
    cool:{
        label:"consumption_cooling",
        tableList:["heating_cooling_meter"],
    },
    electric:{
        label:"active_energy_consumption",
        tableList:["electric"],
    },
    flow:{
        label:"accumulated_flow_consumption",
        tableList:["flow_meter"],
    }
}
const timeFormatType = {
    day:"%Y-%m-%d",
    days:"%Y-%m-%d",
    month:"%Y-%m",
    months:"%Y-%m",
    year:"%Y",
    years:"%Y",
}

const util = {
    /**
     * 开始结束时间的类型处理
     *
     * @param {string} type  - 查询仪表类型。
     * @param {string} domainId  - 区域id。
     * @param {string} timeType  - 时间类型。
     * @param {Array} timeRange  - 查询时间区间数组。
     * @param {Object} queryType  - 查询类型 project - 项目,range - 区域。
     * @param {Array} rangeIds  - 需要查询的区域id数组。不传则是全部区域
     * @param {string} timeGroupType  - 根据时间分类的类型。
     * @return {object} {startTime:处理后的开始时间,endTime:处理后的结束时间,queryGroupTime:处理后时间的sql}。
     */
    createSql:function (type,domainId,timeType,timeRange,queryType,rangeIds,timeGroupType){
        let tables = deviceTypeInfo[type];
        let dateFormat = util.dateFormat(timeType,timeGroupType);
        let timeRangeSql = util.getTimeRange(timeRange);
        let isUnion = tables.tableList.length > 1;
        let sql = ``;
        let queryTypeSql = util.getQueryTypeSql(queryType,rangeIds);
        let queryIds = [];
        for(let table of tables.tableList){
            if(isUnion && tables.tableList.indexOf(table) > 0){
                sql += ` UNION `;
            }
            sql += `SELECT
                         SUM(IFNULL(dd.${tables.label},0)) AS consumption,
                         DATE_FORMAT( dd.data_time, "${dateFormat.data}" ) AS dataTime,
                         DATE_FORMAT( dd.data_time, "${dateFormat.group}" ) AS dataTimeGroup,
                         ${queryTypeSql.column}
                     FROM
                     data_${table}_${domainId} dd
                     INNER JOIN device_file df ON df.device_no = dd.device_no AND df.delete_sign = 1 AND df.parent_id = 0 ${queryTypeSql.dev} 
                     ${queryTypeSql.join}
                     WHERE 1=1 ${timeRangeSql} 
                     ${queryTypeSql.group}
                     `;
            queryIds.push(rangeIds);
        }
        if(isUnion){
            sql = `SELECT
                    SUM(uni.consumption) AS consumption,
                    uni.dataTime AS dataTime,
                    uni.dataTimeGroup AS dataTimeGroup,
                    ${queryTypeSql.uniColumn}
                FROM
                    (${sql}) uni
                ${queryTypeSql.group}`
        }
        return {sql:sql,queryIds:queryIds}
    },
    dateFormat:function(timeType,timeGroupType){
        let dateFormat = timeFormatType[timeType] ? timeFormatType[timeType] : "%Y-%m-%d %H:%i:%s";
        let groupFormat = timeGroupType ? timeFormatType[timeGroupType] : dateFormat;
        return {data:dateFormat,group:groupFormat};
    },
    getTimeRange:function (timeRange,){
        let timeRangeSql = `AND `;
        for (const range of timeRange){
            if(timeRange.indexOf(range) > 0){
                timeRangeSql += `OR`
            }
            timeRangeSql += ` (dd.data_time >= "${range.startTime}" AND dd.data_time <= "${range.endTime}") `
        }
        return timeRangeSql;
    },
    getQueryTypeSql:function (queryType,rangeIds){
        let deviceJudgmentSql = ``;
        let groupBySql = ``;
        let queryColumn = ``;
        let uniQueryColumn = ``;
        let joinSql = ``;
        let topLevelJudgmentSql = ``;
        if(queryType.type === "range"){

            queryColumn = `tlr.range_name AS rangeName,
                           tlr.range_id AS rangeId`;
            uniQueryColumn = `uni.rangeName AS rangeName,
                              uni.rangeId AS rangeId`
            if(queryType.query === "total"){
                groupBySql = ` GROUP BY dataTime,rangeId`;
                if(rangeIds){
                    topLevelJudgmentSql =  ` AND rf.top_level_id IN (?)`
                }
            }else if(queryType.query === "detail"){
                deviceJudgmentSql = ` AND df.range_id IN (?)`;
                groupBySql = ` GROUP BY dataTime`
            }
            joinSql = `INNER JOIN range_file rf ON df.range_id = rf.range_id AND rf.delete_sign = 1 ${topLevelJudgmentSql} 
                       INNER JOIN range_file tlr ON tlr.range_id = rf.top_level_id `
        }else if(queryType.type === "project"){
            if(rangeIds){
                topLevelJudgmentSql =  ` AND pdm.project_id IN (?)`
            }
            joinSql = `INNER JOIN project_device_mid pdm ON pdm.device_id = df.device_id ${topLevelJudgmentSql} 
                       INNER JOIN project_file pf ON pf.project_id = pdm.project_id AND pf.delete_sign = 1`;
            queryColumn = `pf.project_name AS projectName,
                           pf.project_id AS projectId,
                           df.device_id AS deviceId,
                           df.parent_id AS deviceParentId`;
            uniQueryColumn = `uni.projectName AS projectName,
                              uni.projectId AS projectId,
                              uni.deviceId AS deviceId,
                              uni.deviceParentId AS deviceParentId`
            groupBySql = ` GROUP BY dataTime,projectId,deviceId`;
        }
        return {dev:deviceJudgmentSql,group:groupBySql,join:joinSql,column:queryColumn,uniColumn:uniQueryColumn}
    },
    //项目设备数组去重父子集设备
    projectDeviceDataUniq(dataList){
        let parentGroup = _.groupBy(dataList,"deviceParentId");
        let lastDataList = [];
        if(parentGroup[`0`]){
            let parentObj = {};
            for(const device of parentGroup[`0`]){
                parentObj[device.deviceId] = true;
                lastDataList.push(device)
            }
            for(const deviceParentId in parentGroup){
                let deviceDataList = parentGroup[deviceParentId];
                if(deviceParentId !== "0"){
                    for(const device of deviceDataList){
                        if(!parentObj[device.deviceParentId]){
                            lastDataList.push(device)
                        }
                    }
                }
            }
        }else{
            lastDataList = dataList;
        }
        return lastDataList;
    },

    //饼状图时间区间
    pieTimeRange(type){
        let timeRange = [{startTime:moment().format("YYYY-MM-DD 00:00:00"),endTime:moment().format("YYYY-MM-DD 23:59:59")}];
        let timeType = "day";
        switch (type){
            case 2:
                timeRange = [{startTime:moment().subtract(1,"day").format("YYYY-MM-DD 00:00:00"),endTime:moment().subtract(1,"day").format("YYYY-MM-DD 23:59:59")}]
                break;
            case 3:
                timeRange = [{startTime:moment().format("YYYY-MM-01 00:00:00"),endTime:moment().endOf("month").format("YYYY-MM-DD HH:mm:ss")}]
                timeType = "month";
                break;
            case 4:
                timeRange = [{startTime:moment().subtract(1,"month").format("YYYY-MM-01 00:00:00"),endTime:moment().subtract(1,"month").endOf("month").format("YYYY-MM-DD HH:mm:ss")}]
                timeType = "month";
                break;
        }
        return {range:timeRange,type:timeType}
    },

    //月时间区间
    monthTimeRange(id){
        let dateGroupType = [moment().format("YYYY-MM"),
            moment().subtract(1,"year").format("YYYY-MM"),
            moment().subtract(1,"month").format("YYYY-MM")]   //查询月份
        let timeRange = [{startTime:moment().format("YYYY-MM-01 00:00:00"),endTime:moment().endOf("month").format("YYYY-MM-DD HH:mm:ss")},
            {startTime:moment().subtract(1,"year").format("YYYY-MM-01 00:00:00"),endTime:moment().subtract(1,"year").endOf("month").format("YYYY-MM-DD HH:mm:ss")},
            {startTime:moment().subtract(1,"month").format("YYYY-MM-01 00:00:00"),endTime:moment().subtract(1,"month").endOf("month").format("YYYY-MM-DD HH:mm:ss")},
        ];
        let timeType = "month";
        let timeGroupType = null;
        let ids = null;
        if(id && id !=="0"){
            timeRange = [{startTime:moment().format("YYYY-MM-01 00:00:00"),endTime:moment().endOf("month").format("YYYY-MM-DD HH:mm:ss")},
                {startTime:moment().subtract(1,"year").format("YYYY-MM-DD 00:00:00"),endTime:moment().subtract(1,"year").endOf("month").format("YYYY-MM-DD HH:mm:ss")}
            ];
            let currentMonth = dateGroupType[0];
            let yoyMonth = dateGroupType[1];
            dateGroupType = {};
            dateGroupType[currentMonth] = [];
            dateGroupType[yoyMonth] = [];
            let startDate = moment().format("YYYY-MM-01");
            let endDate = moment().format("YYYY-MM-DD");
            while (startDate !== endDate){
                dateGroupType[currentMonth].push(startDate);
                dateGroupType[yoyMonth].push(moment(startDate).subtract(1,"year").format("YYYY-MM-DD"));
                startDate =  moment(startDate).add(1,"day").format("YYYY-MM-DD");
            }
            dateGroupType[currentMonth].push(endDate);
            dateGroupType[yoyMonth].push(moment(endDate).subtract(1,"year").format("YYYY-MM-DD"));
            timeType = "day";
            timeGroupType = "month";
            ids = [id]
        }
        return {type:timeType,range:timeRange,ids:ids,groupType:timeGroupType,dateGroupType:dateGroupType}
    },

    //今日时间区间
    todayTimeRange(){
        let queryYears = [moment().format("YYYY-MM-DD"),        //当天
            moment().subtract(1,"years").format("YYYY-MM-DD"),    //同比
            moment().subtract(1,"days").format("YYYY-MM-DD")]     //环比
        let timeRange = [{startTime:`${queryYears[0]} 00:00:00`,endTime:`${queryYears[0]} 23:59:59`},
            {startTime:`${queryYears[1]} 00:00:00`,endTime:`${queryYears[1]} 23:59:59`},
            {startTime:`${queryYears[2]} 00:00:00`,endTime:`${queryYears[2]} 23:59:59`}];
        return {range:timeRange,queryTime:queryYears}
    },

    //30天趋势时间区间
    trendTimeRange(){
        let dateList = []   //日期数组
        let startDate = moment().subtract(1,"month").format("YYYY-MM-DD");
        let endDate = moment().format("YYYY-MM-DD");
        while (startDate !== endDate){
            dateList.push(startDate);
            startDate = moment(startDate).add(1,"day").format("YYYY-MM-DD");
        }
        dateList.push(endDate);
        let timeRange = [{startTime:moment().subtract(1,"month").format("YYYY-MM-DD 00:00:00"),endTime:moment().format("YYYY-MM-DD 23:59:59")}];
        return {range:timeRange,dateList:dateList};
    }
}
module.exports = util;